﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Text;
using System.Data.SqlClient;
using System.Data;
using System.Web.SessionState;

namespace Web.User.Query
{
    /// <summary>
    /// depositor 的摘要说明
    /// </summary>
    public class depositor : IHttpHandler, IRequiresSessionState
    {

        public void ProcessRequest(HttpContext context)
        {
            context.Response.ContentType = "text/plain";
            common.IsLogin();
            if (context.Request.QueryString["type"] != null)
            {
                string strType = context.Request.QueryString["type"].ToString();
                switch (strType)
                {
                    case "GetWebSiteByUserID": GetWebSiteByUserID(context); break;

                    case "Get_Address_Xian": Get_Address_Xian(context); break;
                    case "Get_Address_Xiang": Get_Address_Xiang(context); break;
                    case "Get_Address_Cun": Get_Address_Cun(context); break;

                    case "GetNewAccountNumber": GetNewAccountNumber(context); break;



                    case "GetByAccountNumver_Depositor": GetByAccountNumver_Depositor(context); break;
                    case "Add_Depositor": Add_Depositor(context); break;
                    case "GetDepositor": GetDepositor(context); break;
                    case "Update_Depositor": Update_Depositor(context); break;
                    case "Update_Address_Cun": Update_Address_Cun(context); break;
                    case "DeleteByID_Address_Cun": DeleteByID_Address_Cun(context); break;
                }
            }

        }

        /// <summary>
        /// 由当前登陆用户的ID获取其所在的网点信息
        /// </summary>
        /// <param name="context"></param>
        void GetWebSiteByUserID(HttpContext context)
        {
            string ID = context.Session["ID"].ToString();
            StringBuilder strSql = new StringBuilder();
            strSql.Append(" SELECT A.ID,A.SerialNumber,A.strName,A.strAddress,A.WBType_ID,numSettle,numAgent,ISAllowBackUp,ISHQ ");
            strSql.Append(" FROM dbo.WB A INNER JOIN dbo.Users B ON A.ID=B.WB_ID");
            strSql.Append(" WHERE B.ID= " + ID);
            DataTable dt = SQLHelper.ExecuteDataTable(strSql.ToString());

            if (dt != null && dt.Rows.Count != 0)
            {
                context.Response.Write(JsonHelper.ToJson(dt));
            }
            else
            {
                context.Response.Write("Error");
            }
        }

        void Get_Address_Xian(HttpContext context)
        {
            StringBuilder strSql = new StringBuilder();
            strSql.Append(" SELECT ID,strName FROM dbo.BD_Address_Xian ");
            strSql.Append(" ORDER BY ISDefault DESC,numSort ASC ");

            DataTable dt = SQLHelper.ExecuteDataTable(strSql.ToString());

            if (dt != null && dt.Rows.Count != 0)
            {
                context.Response.Write(JsonHelper.ToJson(dt));
            }
            else
            {
                context.Response.Write("Error");
            }
        }

        void Get_Address_Xiang(HttpContext context)
        {
            string XianID = context.Request.QueryString["XianID"].ToString();
            StringBuilder strSql = new StringBuilder();
            strSql.Append(" SELECT ID,strName,WBID FROM dbo.BD_Address_Xiang ");
            strSql.Append(" where XianID=" + XianID);
            strSql.Append(" ORDER BY ISDefault DESC,numSort ASC ");

            DataTable dt = SQLHelper.ExecuteDataTable(strSql.ToString());

            if (dt != null && dt.Rows.Count != 0)
            {
                context.Response.Write(JsonHelper.ToJson(dt));
            }
            else
            {
                context.Response.Write("Error");
            }
        }

        void Get_Address_Cun(HttpContext context)
        {
            string XiangID = context.Request.QueryString["XiangID"].ToString();
            StringBuilder strSql = new StringBuilder();
            strSql.Append(" SELECT ID,strName,WBID FROM dbo.BD_Address_Cun ");
            strSql.Append(" where XiangID=" + XiangID);
            strSql.Append(" ORDER BY ISDefault DESC,numSort ASC ");

            DataTable dt = SQLHelper.ExecuteDataTable(strSql.ToString());

            if (dt != null && dt.Rows.Count != 0)
            {
                context.Response.Write(JsonHelper.ToJson(dt));
            }
            else
            {
                context.Response.Write("Error");
            }
        }

        void GetNewAccountNumber(HttpContext context)
        {
            string WB_ID = context.Session["WB_ID"].ToString();

            string strSqlNum = "SELECT SerialNumber FROM dbo.WB WHERE ID="+WB_ID;
            string SerialNumber = SQLHelper.ExecuteScalar(strSqlNum).ToString();
            string AccountNumver = SerialNumber + "0000001";

            StringBuilder strSql = new StringBuilder();
            strSql.Append(" SELECT TOP 1 AccountNumber  FROM  dbo.Depositor ");
            strSql.Append("  WHERE WBID=" + WB_ID);
            strSql.Append("  ORDER BY dt_Update DESC ");

           object obj = SQLHelper.ExecuteScalar(strSql.ToString());

           if (obj != null)
            {
                int numIndex = Convert.ToInt32( obj.ToString().Substring(3));
                AccountNumver = SerialNumber + Fun.ConvertIntToString(numIndex+1,7);
                context.Response.Write(AccountNumver);
            }
            else
            {
                context.Response.Write(AccountNumver);
            }
        }


        void GetByAccountNumver_Depositor(HttpContext context)
        {
            string AccountNumber = context.Request.QueryString["AccountNumber"].ToString();

            StringBuilder strSql = new StringBuilder();
            strSql.Append(" select ID,WBID,AccountNumber,strPassword,CunID as  BD_Address_CunID,strAddress,strName,IDCard,PhoneNO,ISSendMessage,BankCardNO,numState,dt_Add,dt_Update");
            strSql.Append(" FROM dbo.Depositor ");

            strSql.Append(" where AccountNumber=@AccountNumber ");
            SqlParameter[] parameters = {
					new SqlParameter("@AccountNumber", SqlDbType.NVarChar,50)};
            parameters[0].Value = AccountNumber;
            DataTable dt = SQLHelper.ExecuteDataTable(strSql.ToString(), parameters);

            if (dt != null && dt.Rows.Count != 0)
            {
                context.Response.Write(JsonHelper.ToJson(dt));
            }
            else
            {
                context.Response.Write("Error");
            }
        }

        void Add_Depositor(HttpContext context)
        {
          
            string WBID = context.Session["WB_ID"].ToString();
            string strName = context.Request.Form["strName"].ToString();
            string AccountNumber = context.Request.Form["AccountNumber"].ToString();
            string strPassword = context.Request.Form["strPassword"].ToString();
            strPassword = Fun.GetMD5_32(strPassword);

            string XianID = context.Request.Form["XianID"].ToString();
            string XiangID = context.Request.Form["XiangID"].ToString();
            string CunID = context.Request.Form["CunID"].ToString();
            string IDCard = context.Request.Form["IDCard"].ToString();
            string PhoneNO = context.Request.Form["PhoneNO"].ToString();
            bool ISSendMessage = false;
            if (context.Request.Form["ISSendMessage"] == null) {
                ISSendMessage = true;
            }
            string strAddress = context.Request.Form["strAddress"].ToString();

            string strSameAccount = "  SELECT  COUNT(ID)  FROM dbo.Depositor WHERE AccountNumber='" + AccountNumber + "'";
            if (SQLHelper.ExecuteScalar(strSameAccount).ToString() != "0")
            {
                context.Response.Write("1");
                return;
            }


            //string strCount = "   SELECT COUNT(ID) FROM dbo.Depositor WHERE IDCard='" + IDCard + "' AND WBID=" + WBID;
            //if (SQLHelper.ExecuteScalar(strCount).ToString() != "0")
            //{
            //    context.Response.Write("1");
            //    return;
            //}
            StringBuilder strSql = new StringBuilder();
            strSql.Append("insert into [Depositor] (");
            strSql.Append("WBID,AccountNumber,strPassword,strAddress,XianID,XiangID,CunID,strName,IDCard,PhoneNO,ISSendMessage,BankCardNO,numState,dt_Add,dt_Update)");
            strSql.Append(" values (");
            strSql.Append("@WBID,@AccountNumber,@strPassword,@strAddress,@XianID,@XiangID,@CunID,@strName,@IDCard,@PhoneNO,@ISSendMessage,@BankCardNO,@numState,@dt_Add,@dt_Update)");
            strSql.Append(";select @@IDENTITY");
            SqlParameter[] parameters = {
					new SqlParameter("@WBID", SqlDbType.Int,4),
					new SqlParameter("@AccountNumber", SqlDbType.NVarChar,50),
					new SqlParameter("@strPassword", SqlDbType.NVarChar,50),
					new SqlParameter("@strAddress", SqlDbType.NVarChar,100),
					new SqlParameter("@XianID", SqlDbType.Int,4),
					new SqlParameter("@XiangID", SqlDbType.Int,4),
					new SqlParameter("@CunID", SqlDbType.Int,4),
					new SqlParameter("@strName", SqlDbType.NVarChar,50),
					new SqlParameter("@IDCard", SqlDbType.NVarChar,50),
					new SqlParameter("@PhoneNO", SqlDbType.NVarChar,50),
					new SqlParameter("@ISSendMessage", SqlDbType.Bit,1),
					new SqlParameter("@BankCardNO", SqlDbType.NVarChar,50),
					new SqlParameter("@numState", SqlDbType.Int,4),
					new SqlParameter("@dt_Add", SqlDbType.DateTime),
					new SqlParameter("@dt_Update", SqlDbType.DateTime)};
            parameters[0].Value = WBID;
            parameters[1].Value = AccountNumber;
            parameters[2].Value = strPassword;
            parameters[3].Value = strAddress;
            parameters[4].Value = XianID;
            parameters[5].Value = XiangID;
            parameters[6].Value = CunID;
            parameters[7].Value = strName;
            parameters[8].Value = IDCard;
            parameters[9].Value = PhoneNO;
            parameters[10].Value = ISSendMessage;
            parameters[11].Value = 0;
            parameters[12].Value = 1;
            parameters[13].Value = DateTime.Now;
            parameters[14].Value = DateTime.Now;
            if (SQLHelper.ExecuteNonQuery(strSql.ToString(), parameters) != 0)
            {
                context.Response.Write("OK");
            }
            else
            {
                context.Response.Write("Error");
            }
        }

        //根据账号获取单个储户的信息
        void GetDepositor(HttpContext context)
        {

            string WBID = context.Session["WB_ID"].ToString();
            object objISHQ = SQLHelper.ExecuteScalar(" SELECT ISHQ FROM dbo.WB WHERE ID="+WBID);
           
            string AccountNumber = context.Request.QueryString["AccountNumber"].ToString();

            StringBuilder strSql = new StringBuilder();
            strSql.Append("  select A.ID,B.strName AS WBID,A. AccountNumber,strPassword,E.ID AS XianID,D.ID AS XiangID,C.ID AS CunID,A.strAddress,A.strName,IDCard,PhoneNO,ISSendMessage,BankCardNO,numState,A.dt_Add,A.dt_Update");
            strSql.Append("  FROM dbo.Depositor A INNER JOIN dbo.WB B ON A.WBID=B.ID");
            strSql.Append("  INNER JOIN dbo.BD_Address_Cun C ON A.CunID =C.ID");
            strSql.Append("  INNER JOIN dbo.BD_Address_Xiang D ON C.XiangID=D.ID");
            strSql.Append("  INNER JOIN dbo.BD_Address_Xian E ON D.XianID=E.ID");
            strSql.Append("  ");
           
            if (!(Convert.ToBoolean(objISHQ)))//如果不是总部，则只能修改自己的社员
            {
                strSql.Append("  where B.ID="+WBID);
            }

            strSql.Append(" and A.AccountNumber=@AccountNumber ");
            SqlParameter[] parameters = {
					new SqlParameter("@AccountNumber", SqlDbType.NVarChar,50)};
            parameters[0].Value = AccountNumber;
            DataTable dt = SQLHelper.ExecuteDataTable(strSql.ToString(), parameters);

            if (dt != null && dt.Rows.Count != 0)
            {
                context.Response.Write(JsonHelper.ToJson(dt));
            }
            else
            {
                context.Response.Write("Error");
            }
        }

        void Update_Depositor(HttpContext context)
        {

            string WBID = context.Session["WB_ID"].ToString();
            string strName = context.Request.Form["strName"].ToString();
            string AccountNumber = context.Request.Form["AccountNumber"].ToString();
            string strPassword = context.Request.Form["strPassword"].ToString();

            string XianID = context.Request.Form["XianID"].ToString();
            string XiangID = context.Request.Form["XiangID"].ToString();
            string BD_Address_CunID = context.Request.Form["CunID"].ToString();
            string strAddress = context.Request.Form["strAddress"].ToString();
            string IDCard = context.Request.Form["IDCard"].ToString();
            string PhoneNO = context.Request.Form["PhoneNO"].ToString();
            int ISSendMessage = 0;
            if (context.Request.Form["ISSendMessage"] == null)
            {
                ISSendMessage = 1;
            }
         


            StringBuilder strSql = new StringBuilder();
            strSql.Append(" UPDATE dbo.Depositor  SET");
            strSql.Append(" strName='"+strName+"'");
            if (strPassword.Trim() != "")
            {
                strPassword = Fun.GetMD5_32(strPassword);
                strSql.Append(" ,strPassword='" + strPassword + "'");
            }
            strSql.Append(" ,XianID=" + XianID);
            strSql.Append(" ,XiangID=" + XiangID);
            strSql.Append(" ,CunID=" + BD_Address_CunID);
            strSql.Append(" ,strAddress='" + strAddress+"'");
            strSql.Append(" ,IDCard='" + IDCard + "'");
            strSql.Append(" ,PhoneNO='" + PhoneNO + "'");
            strSql.Append(" ,ISSendMessage="+ ISSendMessage);
           
            strSql.Append(" WHERE AccountNumber='" + AccountNumber + "'");


            if (SQLHelper.ExecuteNonQuery(strSql.ToString()) != 0)
            {
                context.Response.Write("OK");
            }
            else
            {
                context.Response.Write("Error");
            }
        }

        void Update_Address_Cun(HttpContext context)
        {
            string ID = context.Request.QueryString["ID"].ToString();
            string strName = context.Request.Form["strName"].ToString();

            StringBuilder strSql = new StringBuilder();
            strSql.Append("update [BD_Address_Cun] set strName='" + strName + "' where ID= " + ID);


            if (SQLHelper.ExecuteNonQuery(strSql.ToString()) != 0)
            {
                context.Response.Write("OK");
            }
            else
            {
                context.Response.Write("Error");
            }
        }

        void DeleteByID_Address_Cun(HttpContext context)
        {
            string wbid = context.Request.QueryString["ID"].ToString();

            StringBuilder strSql = new StringBuilder();
            strSql.Append("delete from [BD_Address_Cun] ");
            strSql.Append(" where ID=@ID ");
            SqlParameter[] parameters = {
					new SqlParameter("@ID", SqlDbType.Int,4)};
            parameters[0].Value = wbid;


            if (SQLHelper.ExecuteNonQuery(strSql.ToString(), parameters) != 0)
            {
                context.Response.Write("OK");
            }
            else
            {
                context.Response.Write("Error");
            }
        }

        public bool IsReusable
        {
            get
            {
                return false;
            }
        }
    }
}